---
title: Queries
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

The core of `nestjs-query` is the `Query`, it is used by `@nestjs-query/query-graphql`, `@nestjs-query/query-typeorm`
`@nestjs-query/query-sequelize`, `@nestjs-query/query-mongoose` and `@nestjs-query/query-typegoose`.

The query interface contains three optional fields.

- `filter`
- `paging`
- `sorting`

All examples will be based on the following class.

```ts
interface MyClass {
  title: string;
  completed: boolean;
  age: number;
}
```

## Filtering

The `filter` field allows the filtering of fields based on the shape of the object the filter is used for.

See the [filter reference](#filter-reference) for a complete list of comparisons available.

:::note
The `Filter` interface is typesafe and the typescript compiler will complain if you include extra fields that are not present on the type you are creating the query for.
:::

Lets create a simple filter that would allow us to filter for titles equal to `'Foo Bar'`

### Simple

```ts
import { Query } from '@nestjs-query/core';

const q: Query<MyClass> = {
  filter: {
    title: { eq: 'Foo Bar' },
  },
};
```

### Multiple Fields

You can also filter on multiple fields.

```ts
import { Query } from '@nestjs-query/core';

const q: Query<MyClass> = {
  filter: {
    // title = 'Foo Bar' AND completed IS TRUE and age > 10
    title: { eq: 'Foo Bar' },
    completed: { is: true },
    age: { gt: 10 },
  },
};
```

### Multiple Comparisons on a single field.

If you include multiple comparisons for a single field they will be ORed together.

```ts
import { Query } from '@nestjs-query/core';

const q: Query<MyClass> = {
  filter: {
    // title = 'Foo Bar' OR field LIKE '%foo%'
    title: { eq: 'Foo Bar', like: '%foo%' },
  },
};
```

### And/Or

The filter also allows for more complex `and` and `or` filters. The `and` and `or` accept an array of filters allowing
for nested complex queries.

In this example we `AND` two filters for the same property together: `age >= 10 AND age <= 20`.

```ts
const q: Query<MyClass> = {
  filter: {
    and: [{ age: { gte: 10 } }, { age: { lte: 20 } }],
  },
};
```

In this example a simple `OR` condition is created: `age >= 10 OR title NOT LIKE '%bar'`

```ts
const q: Query<MyClass> = {
  filter: {
    or: [{ age: { gte: 10 } }, { title: { notLike: '%bar' } }],
  },
};
```

This example combines `AND` and `OR` filters: `age >= 10 AND (title LIKE '%bar' OR title = 'foobar')`.

```ts
const q: Query<MyClass> = {
  filter: {
    and: [
      { age: { gte: 10 } },
      {
        or: [{ title: { like: '%bar' } }, { title: { eq: 'foobar' } }],
      },
    ],
  },
};
```

---

## Paging

The `core` package defines a basic paging interface has two optional fields `limit` and `offset`.

<Tabs
  defaultValue="limit-offset"
  values={[
    { label: 'Limit And Offset', value: 'limit-offset', },
    { label: 'Limit', value: 'limit', },
    { label: 'Offset', value: 'offset', },
  ]
}>
<TabItem value="limit-offset">

```ts
const q: Query<MyClass> = {
  paging: {
    limit: 10,
    offset: 10,
  },
};
```

</TabItem>
<TabItem value="limit">

```ts
const q: Query<MyClass> = {
  paging: {
    limit: 20,
  },
};
```

</TabItem>
<TabItem value="offset">

```ts
const q: Query<MyClass> = {
  paging: {
    offset: 10,
  },
};
```

</TabItem>
</Tabs>

:::note
When using filters on relations with `typeorm` in combination with paging, performance can be degraded on large result
sets. For more info see this [issue](https://github.com/doug-martin/nestjs-query/issues/954)

In short two queries will be executed:
* The first one fetching a distinct list of primary keys with paging applied.
* The second uses primary keys from the first query to fetch the actual records.
:::

---

## Sorting

The `sorting` field allows to specify the sort order for your query.

The `sorting` field is an array of object containing:

- `field` - the field to sort on
- `direction` - `ASC` or `DESC`
- `nulls?` - Optional nulls sort, `NULLS_FIRST` or `NULLS_LAST`

<Tabs
  defaultValue="single"
  values={[
    { label: 'Single-Sort', value: 'single', },
    { label: 'Multi-Sort', value: 'multi', },
  ]
}>
<TabItem value="single">

```ts
// import { SortDirection } from '@nestjs-query/core';

const q: Query<MyClass> = {
  sorting: [{ field: 'title', direction: SortDirection.DESC }],
};
```

</TabItem>
<TabItem value="multi">

```ts
// import { SortDirection } from '@nestjs-query/core';

const q: Query<MyClass> = {
  sorting: [
    { field: 'title', direction: SortDirection.DESC },
    { field: 'age', direction: SortDirection.ASC },
  ],
};
```

</TabItem>
</Tabs>

## Filter Reference

The `filter` option supports the following field comparisons.

:::note
The following examples show an approximation of the SQL that will be generated. The ORM will take care of handling the dialect specifics
:::

### Common Comparisons

All types support the following comparisons.

- `is` - Check is a field is `null`, `true` or `false`.
  ```ts
  // title IS NULL
  {
    title: {
      is: null;
    }
  }
  // completed IS TRUE
  {
    completed: {
      is: true;
    }
  }
  // completed IS false
  {
    completed: {
      is: false;
    }
  }
  ```
- `isNot` - Check is a field is not `null`, `true` or `false`.
  ```ts
  // title IS NOT NULL
  {
    title: {
      isNot: null;
    }
  }
  // completed IS NOT TRUE
  {
    completed: {
      isNot: true;
    }
  }
  // completed IS NOT false
  {
    completed: {
      isNot: false;
    }
  }
  ```
- `neq` - field is not equal to a value.
  ```ts
  // title != 'foo'
  {
    title: {
      neq: 'foo';
    }
  }
  ```
- `gt` - field is greater than a value.
  ```ts
  // title > 'foo'
  {
    title: {
      gt: 'foo';
    }
  }
  ```
- `gte` - field is greater than or equal to a value.
  ```ts
  // title >= 'foo'
  {
    title: {
      gte: 'foo';
    }
  }
  ```
- `lt` - field is less than a value.
  ```ts
  // title < 'foo'
  {
    title: {
      lt: 'foo';
    }
  }
  ```
- `lte` - field is less than or equal to a value.
  ```ts
  // title <= 'foo'
  {
    title: {
      lte: 'foo';
    }
  }
  ```
- `in` - field is in a list of values.
  ```ts
  // title IN ('foo', 'bar', 'baz')
  { title: { in: ['foo', 'bar', 'baz'] } }
  ```
- `notIn` - field is not in a list of values.
  ```ts
  // title NOT IN ('foo', 'bar', 'baz')
  {
    title: {
      notIn: ['foo', 'bar', 'baz'];
    }
  }
  ```

### String Comparisons

- `like` - field is like a value (case sensitive).
  ```ts
  // title LIKE 'Foo%'
  {
    title: {
      like: 'Foo%';
    }
  }
  ```
- `notLike` - field is not like a value (case sensitive).
  ```ts
  // title NOT LIKE 'Foo%'
  {
    title: {
      notLike: 'Foo%';
    }
  }
  ```
- `iLike` - field is like a value (case insensitive).
  ```ts
  // title ILIKE 'Foo%'
  {
    title: {
      iLike: 'Foo%';
    }
  }
  ```
- `notILike` - field is not like a value (case insensitive).
  ```ts
  // title NOT ILIKE 'Foo%'
  {
    title: {
      notILike: 'Foo%';
    }
  }
  ```
